﻿using System.Linq;
using System.Data.Linq;
using UniMana.Data;
using System;
namespace UniMana.Data
{
    public static class Training_SubjectData_Chi
    {
        /// <summary>
        /// lấy danh sách môn học theo khoa
        /// </summary>
        /// <param name="subject"></param>
        /// <param name="departmentKey"></param>
        /// <returns></returns>
        public static IQueryable
            GetList(this Table<Training_Subject> subject, int departmentKey)
        {
            return subject.Where(p => p.Catalogue_DepartmentKey == departmentKey).Distinct();
        }
        /// <summary>
        /// lấy danh sách môn học theo năm học, học kỳ, khoa
        /// </summary>
        /// <param name="subject"></param>
        /// <param name="schoolYearKey"></param>
        /// <param name="termKey"></param>
        /// <param name="departmentKey"></param>
        /// <returns></returns>
        public static IQueryable<Training_Subject>
            GetSubject_InSubjectClass(this Table<Training_Subject> subject,Table<Training_Subject_Class> subject_Class, 
                                int schoolYearKey, int termKey, int departmentKey)
        {
            var Subject = from sjc in subject_Class
                          join sj in subject on sjc.Training_SubjectKey equals sj.Training_SubjectKey
                          where sjc.Catalogue_SchoolYearKey == schoolYearKey &&
                                sjc.Catalogue_TermKey == termKey &&
                                sj.Catalogue_DepartmentKey == departmentKey &&
                                sjc.Opened == true
                          select sj;
            return Subject.Distinct();
        }
        /// <summary>
        /// lấy dan sách môn học đang mở
        /// </summary>
        /// <param name="subject"></param>
        /// <param name="subject_Open"></param>
        /// <param name="departmentKey"></param>
        /// <returns></returns>
        public static IQueryable<Training_Subject>
            GetList_InSubjectOpen(this Table<Training_Subject> subject, 
                                    Table<Training_Subject_Open> subject_Open,
                                    int departmentKey)
        {
            var Subject = from sj in subject
                          where sj.Catalogue_DepartmentKey == departmentKey &&
                                (from sjo in subject_Open
                                 where sjo.Closed == false || sjo.Closed == null
                                 select sjo.Training_SubjectKey).Contains((int)sj.Training_SubjectKey)
                          select sj;
            return Subject.Distinct();
        }
        /// <summary>
        /// lấy danh sách môn đang mở theo học kỳ và năm học
        /// </summary>
        /// <param name="subject"></param>
        /// <param name="subject_Open"></param>
        /// <param name="departmentKey"></param>
        /// <returns></returns>
        public static IQueryable<Training_Subject> GetList_InSubjectOpen_Byterm_Schoolyear(
                                    this Table<Training_Subject> subject,
                                    Table<Training_Subject_Open> subject_Open,
                                    int departmentKey)
        {
            var Subject = from sj in subject
                          join sjo in subject_Open on sj.Training_SubjectKey equals sjo.Training_SubjectKey
                          where sj.Catalogue_DepartmentKey == departmentKey &&
                                (sjo.Closed == false || sjo.Closed == null)
                          select sj;
            return Subject.Distinct();
        }
        /// <summary>
        /// lay danh sach lop co so sjnh vjen it hon so truyen vao
        /// </summary>
        /// <param name="subject"></param>
        /// <param name="subject_Open"></param>
        /// <param name="departmentKey"></param>
        /// <param name="term"></param>
        /// <param name="schoolyear"></param>
        /// <returns></returns>
        public static IQueryable<Training_Subject> GetList_InSubjectOpen_Byterm_Schoolyear_Min(
                                    this Table<Training_Subject> subject,
                                    Table<Training_Subject_Open> subject_Open,
                                    Table<Training_Student_Subject> student_Subject,
                                    int departmentKey,
                                    int minvalue)
        {
            var Subject_all = from sj in subject
                          join sjo in subject_Open on sj.Training_SubjectKey equals sjo.Training_SubjectKey
                          where sj.Catalogue_DepartmentKey == departmentKey &&
                                (sjo.Closed == false || sjo.Closed == null)
                          select sjo;
            var StudentSubjectCount = from st_sj in student_Subject
                                      join sjo in Subject_all on st_sj.Training_SubjectOpenKey
                                        equals sjo.Training_SubjectOpenKey
                                      where st_sj.Training_SubjectKey == sjo.Training_SubjectKey
                                      group st_sj by new
                                      {
                                          st_sj.Training_SubjectKey,
                                          st_sj.Training_SubjectOpenKey
                                      } into groupby
                                      where groupby.Count() >= minvalue
                                      select new
                                      {
                                          groupby.Key.Training_SubjectOpenKey,
                                          Student = groupby.Count()
                                      };
            var Subject_Min = from sjo in Subject_all
                              join sj in subject on sjo.Training_SubjectKey equals sj.Training_SubjectKey
                              where !(from sc in StudentSubjectCount
                                      select sc.Training_SubjectOpenKey).Contains(sjo.Training_SubjectOpenKey)
                              select sj;
            return Subject_Min.Distinct();
        }
        /// <summary>
        /// Open
        /// </summary>
        /// <param name="subject"></param>
        /// <param name="subject_Open"></param>
        /// <param name="departmentKey"></param>
        /// <returns></returns>
        public static IQueryable GetList_InSubjectOpen_Byterm_Schoolyear_Open(
                                    this Table<Training_Subject> subject,
                                    Table<Training_Subject_Open> subject_Open)
        {
            var Subject = from sj in subject
                          join sjo in subject_Open on sj.Training_SubjectKey equals sjo.Training_SubjectKey
                          where sjo.Closed == false || sjo.Closed == null
                          select new
                          {
                              sj.Accumulated,
                              sj.Catalogue_DepartmentKey,
                              sj.CreatedBy,
                              sj.CreatedOn,
                              sj.Credits1,
                              sj.Credits2,
                              sj.Credits3,
                              sj.Credits4,
                              sj.ID,
                              sj.ModifiedBy,
                              sj.ModifiedOn,
                              sj.Name,
                              sj.Note,
                              sj.Training_SubjectKey,
                              sj.TotalCredits,
                              sjo.Training_SubjectOpenKey
                          };
            return Subject.Distinct();
        }
        /// <summary>
        /// Open
        /// </summary>
        /// <param name="subject"></param>
        /// <param name="subject_Open"></param>
        /// <param name="student_Subject"></param>
        /// <param name="departmentKey"></param>
        /// <param name="minvalue"></param>
        /// <returns></returns>
        public static IQueryable GetList_InSubjectOpen_Byterm_Schoolyear_Min_Open(
                                    this Table<Training_Subject> subject,
                                    Table<Training_Subject_Open> subject_Open,
                                    Table<Training_Student_Subject> student_Subject,
                                    int minvalue)
        {
            var Subject_all = from sj in subject
                              join sjo in subject_Open on sj.Training_SubjectKey equals sjo.Training_SubjectKey
                              where sjo.Closed == false || sjo.Closed == null
                              select sjo;
            var StudentSubjectCount = from st_sj in student_Subject
                                      join sjo in Subject_all on st_sj.Training_SubjectOpenKey
                                        equals sjo.Training_SubjectOpenKey
                                      where st_sj.Training_SubjectKey == sjo.Training_SubjectKey
                                      group st_sj by new
                                      {
                                          st_sj.Training_SubjectKey,
                                          st_sj.Training_SubjectOpenKey
                                      } into groupby
                                      where groupby.Count() >= minvalue
                                      select new
                                      {
                                          groupby.Key.Training_SubjectOpenKey,
                                          Student = groupby.Count()
                                      };
            var Subject_Min = from sjo in Subject_all
                              join sj in subject on sjo.Training_SubjectKey equals sj.Training_SubjectKey
                              where !(from sc in StudentSubjectCount
                                      select sc.Training_SubjectOpenKey).Contains(sjo.Training_SubjectOpenKey)
                              select new
                              {
                                  sj.Accumulated,
                                  sj.Catalogue_DepartmentKey,
                                  sj.CreatedBy,
                                  sj.CreatedOn,
                                  sj.Credits1,
                                  sj.Credits2,
                                  sj.Credits3,
                                  sj.Credits4,
                                  sj.ID,
                                  sj.ModifiedBy,
                                  sj.ModifiedOn,
                                  sj.Name,
                                  sj.Note,
                                  sj.Training_SubjectKey,
                                  sj.TotalCredits,
                                  sjo.Training_SubjectOpenKey
                              };
            return Subject_Min.Distinct();
        }
    }
}
